﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;

namespace DBProvider.Providers
{
	public class ExaminationProvider : IExaminationProvider, IDisposable
	{
		private DBHealthCommunityDataContext _dbContext;

		#region IExaminationProvider Members

		public Examination CreateExamination(Examination theExamination, out string msg)
		{
			if(theExamination == null)
			{
				throw new ArgumentNullException();
			}

			msg = string.Empty;
			Examination result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var isExist = db.Examinations.Count(e => e.ExaminationName == theExamination.ExaminationName) > 0;

					if(isExist)
					{
						msg = "同名的试卷已存在";
					}
					else
					{
						db.Examinations.InsertOnSubmit(theExamination);
						db.SubmitChanges();

						result = db.Examinations.SingleOrDefault(e => e.ExaminationName == theExamination.ExaminationName);
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<ExaminationCategory> GetAllExaminationCategory()
		{
			List<ExaminationCategory> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					result = db.ExaminationCategories.ToList();
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public ExaminationCategory CreateExaminationCategory(ExaminationCategory theNewExmtCat, out string msg)
		{
			if(theNewExmtCat == null)
			{
				throw new ArgumentNullException();
			}

			msg = string.Empty;
			ExaminationCategory result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var isExist = db.ExaminationCategories.Count(ec => ec.ExaminationCategoryName == theNewExmtCat.ExaminationCategoryName) > 0;

					if(!isExist)
					{
						db.ExaminationCategories.InsertOnSubmit(theNewExmtCat);
						db.SubmitChanges();

						result = db.ExaminationCategories.SingleOrDefault(ec => ec.ExaminationCategoryName == theNewExmtCat.ExaminationCategoryName);
					}
					else
					{
						msg = "已存在同名的考试类别";
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public bool UpdateExaminationCategory(ExaminationCategory theExmtCat)
		{
			if(theExmtCat == null || theExmtCat.ExaminationCategoryID <= 0)
			{
				throw new ArgumentException();
			}

			var result = false;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbExmtCat = db.ExaminationCategories.SingleOrDefault(ec => ec.ExaminationCategoryID == theExmtCat.ExaminationCategoryID);

					if(dbExmtCat != null)
					{
						dbExmtCat.ExaminationCategoryDescription = theExmtCat.ExaminationCategoryDescription;
						dbExmtCat.ExaminationCategoryName = theExmtCat.ExaminationCategoryName;

						db.SubmitChanges();

						result = true;
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public bool DeleteExaminationCategory(int exmtCatID)
		{
			if(exmtCatID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			var result = false;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbExmtCat = db.ExaminationCategories.SingleOrDefault(ec => ec.ExaminationCategoryID == exmtCatID);

					if(dbExmtCat != null)
					{
						db.ExaminationCategories.DeleteOnSubmit(dbExmtCat);
						db.SubmitChanges();

						result = true;
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<Examination> GetExaminationByCategory(int exmtCatID, int drugStoreID)
		{
			if(exmtCatID < 0 || drugStoreID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<Examination> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					IQueryable<Examination> data = null;

					if(exmtCatID > 0)
					{
						data = from e in db.Examinations
									 where e.ExaminationCategoryID == exmtCatID && e.DrugStoreID == drugStoreID
									 select e;
					}
					else
					{
						data = from e in db.Examinations
									 where e.DrugStoreID == drugStoreID
									 select e;
					}

					if(data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public Examination GetExamination(int exmtID)
		{
			if(exmtID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			Examination result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					result = db.Examinations.SingleOrDefault(e => e.ExaminationID == exmtID);
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public bool UpdateExamination(Examination theExmt)
		{
			if(theExmt == null || theExmt.ExaminationID <= 0)
			{
				throw new ArgumentException();
			}

			var result = false;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbExamination = db.Examinations.SingleOrDefault(e => e.ExaminationID == theExmt.ExaminationID);

					if(dbExamination != null)
					{
						dbExamination.ExaminationAddDate = theExmt.ExaminationAddDate;
						dbExamination.ExaminationCategoryID = theExmt.ExaminationCategoryID;
						dbExamination.ExaminationDexcription = theExmt.ExaminationDexcription;
						dbExamination.ExaminationName = theExmt.ExaminationName;
						dbExamination.ExaminationTime = theExmt.ExaminationTime;
						dbExamination.ExaminationTotalScore = theExmt.ExaminationTotalScore;
						dbExamination.IsPublic = theExmt.IsPublic;

						db.SubmitChanges();

						result = true;
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public bool DeleteExamination(int exmtID)
		{
			if(exmtID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			var result = false;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbExtnAnswer = from ea in db.ExaminationAnswers
														 where (from eq in db.ExaminationQuestions
																		where eq.ExaminationID == exmtID
																		select eq.ExaminationQuestionID).Contains(ea.ExaminationQuestionID)
														 select ea;

					if(dbExtnAnswer != null)
					{
						var dbUserAndExtnAnswer = from uea in db.RLT_USERANDExaminationAnswers
																			where dbExtnAnswer.Select(ea => ea.ExaminationAnswerID).Contains(uea.ExaminationAnswerID)
																			select uea;

						db.RLT_USERANDExaminationAnswers.DeleteAllOnSubmit(dbUserAndExtnAnswer);
					}

					var dbExtnQstn = from eq in db.ExaminationQuestions
													 where eq.ExaminationID == exmtID
													 select eq;

					var dbUserAndExtn = from ue in db.RLT_UserAndExaminations
															where ue.ExaminationID == exmtID
															select ue;

					var dbRLT_ExaminationAndShareTo = db.RLT_ExaminationAndShareTos.Where(es => es.ExaminationID == exmtID);

					var dbExamination = db.Examinations.SingleOrDefault(e => e.ExaminationID == exmtID);

					if(dbExtnAnswer != null)
					{
						db.ExaminationAnswers.DeleteAllOnSubmit(dbExtnAnswer);
					}
					if(dbExtnQstn != null)
					{
						db.ExaminationQuestions.DeleteAllOnSubmit(dbExtnQstn);
					}
					if(dbUserAndExtn != null)
					{
						db.RLT_UserAndExaminations.DeleteAllOnSubmit(dbUserAndExtn);
					}
					if(dbRLT_ExaminationAndShareTo != null)
					{
						db.RLT_ExaminationAndShareTos.DeleteAllOnSubmit(dbRLT_ExaminationAndShareTo);
					}
					if(dbExamination != null)
					{
						db.Examinations.DeleteOnSubmit(dbExamination);
					}

					db.SubmitChanges();

					result = true;
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<Examination> GetExaminationByUser(int userID)
		{
			if(userID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<Examination> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var data = from e in db.Examinations
										 where (from exmid in db.RLT_UserAndExaminations
														where exmid.UserID == userID
														select exmid.ExaminationID).Contains(e.ExaminationID)
										 select e;

					if(data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<ExaminationQuestion> GetExaminationQuestionByExamination(int exmtID)
		{
			if(exmtID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<ExaminationQuestion> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var data = from eq in db.ExaminationQuestions
										 where eq.ExaminationID == exmtID
										 select eq;

					if(data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public ExaminationQuestion CreateExaminationQuestion(ExaminationQuestion theNewExmtQstn, bool saveAnswers, out string msg)
		{
			if(theNewExmtQstn == null)
			{
				throw new ArgumentNullException();
			}

			msg = string.Empty;
			ExaminationQuestion result = null;

			try
			{
				if(_dbContext == null)
				{
					_dbContext = new DBHealthCommunityDataContext();
				}

				var isExist = _dbContext.ExaminationQuestions.Count(eq => eq.ExaminationID == theNewExmtQstn.ExaminationID && eq.ExaminationQuestionContent == theNewExmtQstn.ExaminationQuestionContent) > 0;

				if(isExist)
				{
					msg = "该试卷中已存在同名的题目";
				}
				else
				{
					_dbContext.ExaminationQuestions.InsertOnSubmit(theNewExmtQstn);
					_dbContext.SubmitChanges();

					result = _dbContext.ExaminationQuestions.SingleOrDefault(eq => eq.ExaminationID == theNewExmtQstn.ExaminationID && eq.ExaminationQuestionContent == theNewExmtQstn.ExaminationQuestionContent);

					if(result != null && saveAnswers && theNewExmtQstn.ExaminationAnswers != null && theNewExmtQstn.ExaminationAnswers.Count > 0)
					{
						foreach(var eqa in theNewExmtQstn.ExaminationAnswers)
						{
							eqa.ExaminationQuestionID = result.ExaminationQuestionID;

							var newEqa = CreateExaminationAnswer(eqa, out msg);

							if(newEqa != null)
							{
								result.ExaminationAnswers = new System.Data.Linq.EntitySet<ExaminationAnswer>();
								result.ExaminationAnswers.Add(newEqa);
							}
						}
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public bool UpdateExaminationQuestion(ExaminationQuestion theExmtQstn)
		{
			if(theExmtQstn == null || theExmtQstn.ExaminationQuestionID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			var result = false;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbEQ = db.ExaminationQuestions.SingleOrDefault(eq => eq.ExaminationQuestionID == theExmtQstn.ExaminationQuestionID);

					if(dbEQ != null)
					{
						dbEQ.ExaminationID = theExmtQstn.ExaminationID;
						dbEQ.ExaminationQuestionAnswer = theExmtQstn.ExaminationQuestionAnswer;
						dbEQ.ExaminationQuestionContent = theExmtQstn.ExaminationQuestionContent;
						dbEQ.ExaminationQuestionScore = theExmtQstn.ExaminationQuestionScore;

						db.SubmitChanges();

						result = true;
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public bool DeleteExaminationQuestion(int exmtQstnID)
		{
			if(exmtQstnID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			var result = false;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbExmtAnswer = from ea in db.ExaminationAnswers
														 where ea.ExaminationQuestionID == exmtQstnID
														 select ea;

					if(dbExmtAnswer != null)
					{
						var dbUserAndExmtAnswer = from uea in db.RLT_USERANDExaminationAnswers
																			where dbExmtAnswer.Select(ea => ea.ExaminationAnswerID).Contains(uea.ExaminationAnswerID)
																			select uea;

						if(dbUserAndExmtAnswer != null)
						{
							db.RLT_USERANDExaminationAnswers.DeleteAllOnSubmit(dbUserAndExmtAnswer);
						}
					}

					db.ExaminationAnswers.DeleteAllOnSubmit(dbExmtAnswer);

					var dbExmtQstn = from eq in db.ExaminationQuestions
													 where eq.ExaminationQuestionID == exmtQstnID
													 select eq;

					if(dbExmtQstn != null)
					{
						db.ExaminationQuestions.DeleteAllOnSubmit(dbExmtQstn);
						db.SubmitChanges();

						result = true;
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<ExaminationAnswer> GetExaminationAnswerByExamination(int exmtID)
		{
			if(exmtID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<ExaminationAnswer> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var data = from ea in db.ExaminationAnswers
										 where (from eq in db.ExaminationQuestions
														where eq.ExaminationID == exmtID
														select eq.ExaminationQuestionID).Contains(ea.ExaminationQuestionID)
										 select ea;

					if(data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<ExaminationAnswer> GetExaminationAnswerByUser(int userID)
		{
			if(userID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<ExaminationAnswer> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var data = from ea in db.ExaminationAnswers
										 where (from uea in db.RLT_USERANDExaminationAnswers
														where uea.UserID == userID
														select uea.ExaminationAnswerID).Contains(ea.ExaminationAnswerID)
										 select ea;

					if(data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public ExaminationAnswer CreateExaminationAnswer(ExaminationAnswer theNewExmtAswr, out string msg)
		{
			if(theNewExmtAswr == null)
			{
				throw new ArgumentNullException();
			}

			ExaminationAnswer result = null;
			msg = string.Empty;

			try
			{
				if(_dbContext == null)
				{
					_dbContext = new DBHealthCommunityDataContext();
				}

				var isExist = _dbContext.ExaminationAnswers.Count(ea => ea.ExaminationAnswerContent == theNewExmtAswr.ExaminationAnswerContent && ea.ExaminationQuestionID == theNewExmtAswr.ExaminationQuestionID) > 0;

				if(isExist)
				{
					msg = "试卷中相同的题目已存在";
				}
				else
				{
					_dbContext.ExaminationAnswers.InsertOnSubmit(theNewExmtAswr);
					_dbContext.SubmitChanges();

					result = _dbContext.ExaminationAnswers.SingleOrDefault(ea => ea.ExaminationQuestionID == theNewExmtAswr.ExaminationQuestionID && ea.ExaminationAnswerContent == theNewExmtAswr.ExaminationAnswerContent);
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public bool UpdateExaminationAnswer(ExaminationAnswer theExmtAswr)
		{
			if(theExmtAswr == null || theExmtAswr.ExaminationAnswerID <= 0)
			{
				throw new ArgumentException();
			}

			var result = false;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbExmtAnswer = db.ExaminationAnswers.SingleOrDefault(ea => ea.ExaminationAnswerID == theExmtAswr.ExaminationAnswerID);

					if(dbExmtAnswer != null)
					{
						dbExmtAnswer.ExaminationAnswerContent = theExmtAswr.ExaminationAnswerContent;
						dbExmtAnswer.ExaminationAnswerNum = theExmtAswr.ExaminationAnswerNum;

						db.SubmitChanges();

						result = true;
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public bool DeleteExaminationAnswer(int exmtAswrID)
		{
			if(exmtAswrID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			var result = false;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbUserAndExmtAnswer = from uea in db.RLT_USERANDExaminationAnswers
																		where uea.ExaminationAnswerID == exmtAswrID
																		select uea;

					if(dbUserAndExmtAnswer != null)
					{
						db.RLT_USERANDExaminationAnswers.DeleteAllOnSubmit(dbUserAndExmtAnswer);
					}

					var dbExmtAnswer = from ea in db.ExaminationAnswers
														 where ea.ExaminationAnswerID == exmtAswrID
														 select ea;

					if(dbExmtAnswer != null)
					{
						db.ExaminationAnswers.DeleteAllOnSubmit(dbExmtAnswer);
						db.SubmitChanges();

						result = true;
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public void CreateRLTUserAndExamination(RLT_UserAndExamination theRLT_UserAndExmt)
		{
			if(theRLT_UserAndExmt == null)
			{
				throw new ArgumentNullException();
			}

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var isExist = db.RLT_UserAndExaminations.Count(ue => ue.ExaminationID == theRLT_UserAndExmt.ExaminationID && ue.UserID == theRLT_UserAndExmt.UserID) > 0;

					if(!isExist)
					{
						db.RLT_UserAndExaminations.InsertOnSubmit(theRLT_UserAndExmt);
						db.SubmitChanges();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}
		}

		public void CreateRLTUserAndExaminationAnswer(List<RLT_USERANDExaminationAnswer> lstRLT_USERANDExaminationAnswer)
		{
			if(lstRLT_USERANDExaminationAnswer == null || lstRLT_USERANDExaminationAnswer.Count <= 0)
			{
				throw new ArgumentException();
			}

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					db.RLT_USERANDExaminationAnswers.InsertAllOnSubmit(lstRLT_USERANDExaminationAnswer);
					db.SubmitChanges();
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}
		}

		#endregion

		#region IDisposable Members

		public void Dispose()
		{
			if(_dbContext != null)
			{
				_dbContext.Dispose();
				_dbContext = null;
			}
		}

		#endregion

		#region IExaminationProvider Members


		public List<RLT_UserAndExamination> GetUserAndExaminationByUser(int userID)
		{
			if(userID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<RLT_UserAndExamination> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					DataLoadOptions dlo = new DataLoadOptions();
					dlo.LoadWith<RLT_UserAndExamination>(e => e.Examination);
					db.LoadOptions = dlo;

					var data = from ue in db.RLT_UserAndExaminations
										 where ue.UserID == userID
										 select ue;

					if(data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		#endregion

		#region IExaminationProvider Members


		public List<RLT_USERANDExaminationAnswer> GetUsersExaminationAnswer(int userID)
		{
			if(userID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<RLT_USERANDExaminationAnswer> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var data = from ue in db.RLT_USERANDExaminationAnswers
										 where ue.UserID == userID
										 select ue;

					if(data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		#endregion

		#region IExaminationProvider Members


		public List<Examination> GetExaminationByCategory(int exmtCatID, int drugStoreID, int pageIndex, int pageSize, out int totalRecords)
		{
			if(exmtCatID < 0 || drugStoreID <= 0 || pageIndex < 0 || pageSize < 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<Examination> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					IQueryable<Examination> data = null;

					if(exmtCatID > 0)
					{
						totalRecords = db.Examinations.Count(e => e.ExaminationCategoryID == exmtCatID && e.DrugStoreID == drugStoreID);

						data = (from e in db.Examinations
										where e.ExaminationCategoryID == exmtCatID && e.DrugStoreID == drugStoreID
										orderby e.ExaminationAddDate descending
										select e).Skip(pageIndex * pageSize).Take(pageSize);
					}
					else
					{
						totalRecords = db.Examinations.Count(e => e.DrugStoreID == drugStoreID);

						data = (from e in db.Examinations
										where e.DrugStoreID == drugStoreID
										orderby e.ExaminationAddDate descending
										select e).Skip(pageIndex * pageSize).Take(pageSize);
					}

					if(data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		#endregion

		#region IExaminationProvider Members


		public List<RLT_ExaminationAndShareTo> GetExaminationAndShareToByExamination(int examinationID)
		{
			if(examinationID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<RLT_ExaminationAndShareTo> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					DataLoadOptions dlo = new DataLoadOptions();
					dlo.LoadWith<RLT_ExaminationAndShareTo>(ms => ms.User);
					db.LoadOptions = dlo;

					var data = from vs in db.RLT_ExaminationAndShareTos
										 where vs.ExaminationID == examinationID
										 select vs;

					if(data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public void CreateExaminationAndShareTo(List<RLT_ExaminationAndShareTo> lstExaminationAndShareTo)
		{
			if(lstExaminationAndShareTo == null || lstExaminationAndShareTo.Count <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					db.RLT_ExaminationAndShareTos.InsertAllOnSubmit(lstExaminationAndShareTo);
					db.SubmitChanges();
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}
		}

		public void SaveExaminationAndShareTo(int examinationID, List<RLT_ExaminationAndShareTo> lstExaminationAndShareTo)
		{
			if(examinationID <= 0 || lstExaminationAndShareTo == null)
			{
				throw new ArgumentOutOfRangeException();
			}

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbExaminationAndShareTo = from vs in db.RLT_ExaminationAndShareTos
																				where vs.ExaminationID == examinationID
																				select vs;

					if(dbExaminationAndShareTo != null)
					{
						var lstDBExaminationAndShareTo = dbExaminationAndShareTo.ToList();

						var deleteItems = lstDBExaminationAndShareTo.Except(lstExaminationAndShareTo);

						if(deleteItems != null)
						{
							db.RLT_ExaminationAndShareTos.DeleteAllOnSubmit(deleteItems);
						}

						var newItems = lstExaminationAndShareTo.Except(lstDBExaminationAndShareTo);

						if(newItems != null)
						{
							db.RLT_ExaminationAndShareTos.InsertAllOnSubmit(newItems);
						}

						db.SubmitChanges();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}
		}

		public bool DeleteExaminationQuestionByExamination(int exmtID)
		{
			if(exmtID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			var result = false;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					var dbExmtAnswer = from ea in db.ExaminationAnswers
														 where (from eq in db.ExaminationQuestions
																		where eq.ExaminationID == exmtID
																		select eq.ExaminationQuestionID).Contains(ea.ExaminationQuestionID)
														 select ea;

					if(dbExmtAnswer != null)
					{
						var dbUserAndExmtAnswer = from uea in db.RLT_USERANDExaminationAnswers
																			where dbExmtAnswer.Select(ea => ea.ExaminationAnswerID).Contains(uea.ExaminationAnswerID)
																			select uea;

						if(dbUserAndExmtAnswer != null)
						{
							db.RLT_USERANDExaminationAnswers.DeleteAllOnSubmit(dbUserAndExmtAnswer);
						}
					}

					db.ExaminationAnswers.DeleteAllOnSubmit(dbExmtAnswer);

					var dbExmtQstn = from eq in db.ExaminationQuestions
													 where eq.ExaminationID == exmtID
													 select eq;

					if(dbExmtQstn != null)
					{
						db.ExaminationQuestions.DeleteAllOnSubmit(dbExmtQstn);
						db.SubmitChanges();

						result = true;
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		public List<Examination> GetExaminationByCategory(int userID, int exmtCatID, int drugStoreID, int pageIndex, int pageSize, out int totalRecords)
		{
			if(exmtCatID < 0 || drugStoreID <= 0 || pageIndex < 0 || pageSize < 0 || userID <= 0)
			{
				throw new ArgumentOutOfRangeException();
			}

			List<Examination> result = null;

			try
			{
				using(DBHealthCommunityDataContext db = new DBHealthCommunityDataContext())
				{
					IQueryable<Examination> data = null;

					var user = db.Users.SingleOrDefault(u => u.UserID == userID);

					if(exmtCatID > 0)
					{
						totalRecords = (from exm in db.Examinations
														where exm.ExaminationCategoryID == exmtCatID && ((exm.DrugStoreID == user.DrugStoreID) || (exm.IsPublic == 1) || ((from es in db.RLT_ExaminationAndShareTos
																																																																							 where es.ShareToDrugStoreID == user.DrugStoreID
																																																																							 select es.ExaminationID).Contains(exm.ExaminationID)) || ((from es in db.RLT_ExaminationAndShareTos
																																																																																																					where es.ShareToMemberID == userID
																																																																																																					select es.ExaminationID).Contains(exm.ExaminationID)))
														select exm).Count();

						data = (from exm in db.Examinations
										where exm.ExaminationCategoryID == exmtCatID && ((exm.DrugStoreID == user.DrugStoreID) || (exm.IsPublic == 1) || ((from es in db.RLT_ExaminationAndShareTos
																																																																			 where es.ShareToDrugStoreID == user.DrugStoreID
																																																																			 select es.ExaminationID).Contains(exm.ExaminationID)) || ((from es in db.RLT_ExaminationAndShareTos
																																																																																																	where es.ShareToMemberID == userID
																																																																																																	select es.ExaminationID).Contains(exm.ExaminationID)))
										orderby exm.ExaminationAddDate descending
										select exm).Skip(pageIndex * pageSize).Take(pageSize);
					}
					else
					{
						totalRecords = (from exm in db.Examinations
														where (exm.DrugStoreID == user.DrugStoreID) || (exm.IsPublic == 1) || ((from es in db.RLT_ExaminationAndShareTos
																																																		where es.ShareToDrugStoreID == user.DrugStoreID
																																																		select es.ExaminationID).Contains(exm.ExaminationID)) || ((from es in db.RLT_ExaminationAndShareTos
																																																																															 where es.ShareToMemberID == userID
																																																																															 select es.ExaminationID).Contains(exm.ExaminationID))
														select exm).Count();

						data = (from exm in db.Examinations
										where (exm.DrugStoreID == user.DrugStoreID) || (exm.IsPublic == 1) || ((from es in db.RLT_ExaminationAndShareTos
																																														where es.ShareToDrugStoreID == user.DrugStoreID
																																														select es.ExaminationID).Contains(exm.ExaminationID)) || ((from es in db.RLT_ExaminationAndShareTos
																																																																											 where es.ShareToMemberID == userID
																																																																											 select es.ExaminationID).Contains(exm.ExaminationID))
										orderby exm.ExaminationAddDate descending
										select exm).Skip(pageIndex * pageSize).Take(pageSize);
					}

					if(data != null)
					{
						result = data.ToList();
					}
				}
			}
			catch(Exception exc)
			{
				throw exc;
			}

			return result;
		}

		#endregion
	}
}
